
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- Crea vista persona
-- drop view persona
create view [dbo].[persona] as
SELECT	distinct Documento as pers_documento,
		case estadocivil 
			when 4 then 8 
			else estadocivil
		end as tab_estado_civil_id,
		--Sexo as pers_sexo,
		CASE Sexo
			WHEN 1 THEN 'F'
			WHEN 2 THEN 'M'
		END AS pers_sexo,
		Apellido as pers_apellido,
		Nombre as pers_nombre,
		FechaNacimiento as pers_fecha_nacimiento,
		Vive as pers_vive,
		NULL as tab_niv_esc_cursa_id
FROM tb_Agentes WHERE Documento not in (SELECT Documento FROM tb_FamiliaresACargo)
UNION
SELECT	distinct Documento as pers_documento,
		case estadocivil 
			when 4 then 8 
			else estadocivil
		end as tab_estado_civil_id,
		--Sexo as pers_sexo,
		CASE Sexo
			WHEN 1 THEN 'F'
			WHEN 2 THEN 'M'
		END AS pers_sexo,
		left(apellidoynombre,CHARINDEX(',',apellidoynombre)-1) as pers_apellido,
		substring(apellidoynombre,CHARINDEX(',',apellidoynombre)+1,50) as pers_nombre,
		FechaDeNacimiento as pers_fecha_nacimiento,
		Vive as pers_vive,
		NivelActual as tab_niv_esc_cursa_id
FROM tb_FamiliaresACargo
--where documento not in (select documento from tb_agentes)


/*
select * from tb_Agentes
select * from tb_EstadosCiviles
select * from tb_familiaresacargo

--Generar vista relacion_persona (con familiares a cargo)!!!!!!
--y vista historia_clinica

*/
GO
